﻿///    /************************************************************************ 
///     * uoLib library for .Net projects.
///     * Copyright (c) 2009 by uonun
///     * Homepage: http://uolib.ourome.com
///     * 
///     * This program is free software: you can redistribute it and/or modify
///     * it under the terms of the GNU General Public License as published by
///     * the Free Software Foundation, either version 3 of the License, or
///     * (at your option) any later version.

///     * This program is distributed in the hope that it will be useful,
///     * but WITHOUT ANY WARRANTY; without even the implied warranty of
///     * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
///     * GNU General Public License for more details.

///     * You should have received a copy of the GNU General Public License
///     * along with this program.  If not, see http://www.gnu.org/licenses/gpl.html.
///     ***********************************************************************/ 

using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using uoLib.Data.Factories;
using System.Collections;
using System.Collections.ObjectModel;
using System.Collections.Generic;

namespace uoLib.Data
{
    /// <summary>
    /// 数据库对象基类。可继承使用。
    /// </summary>
    [HelpAttribute("http://uoLib.ourome.com/")]
    [Author("uonun", Email = "uonun@163.com", HomePage = "http://uoLib.ourome.com/")]
    public abstract class BaseProvider : IBaseProvider, IConnectionFactory
    {
        private IConnectionFactory connFactroy;

        private ConnectionType connType = ConnectionType.Unsupported;
        //private DbConnection _conn = null;

        protected string connStringName;

        internal BaseProvider(ConnectionType connType)
        {
            this.connType = connType;

            //第一步先根据 connType 创建工厂
            this.InitConnFactory(connType);

            //然后根据工厂创建 DbConnectoin 实例
            //this.InitConn();
        }

        private string _connectionString;
        public string ConnectionString
        {
            get { return _connectionString; }
            set { _connectionString = value; }
        }

        private void InitConnFactory(ConnectionType connType)
        {
            switch (connType)
            {
                case ConnectionType.SqlClient:
                    connFactroy = new SqlClientConnFactory(); break;
                case ConnectionType.OleDb:
                    connFactroy = new OleDbConnFactory(); break;
                case ConnectionType.Odbc:
                    connFactroy = new OdbcConnFactory(); break;
                case ConnectionType.OracleClient:
                    connFactroy = new OracleClientConnFactory(); break;
                case ConnectionType.Unsupported:
                default:
                    throw new Exceptions.NotSupportedConnException(this.connType);
            }
        }

        /// <summary>
        /// 执行DbCommand.ExecuteNonQuery()。
        /// </summary>
        /// <param name="cmdStr">要执行的 SQL 语句</param>
        /// <returns></returns>
        /// <remarks>
        /// 与<see cref="DbCommand.ExecuteScalar"/>的区别在于，它在执行前后会自动为你打开和关闭对应的数据库连接。
        /// </remarks>
        public int ExecuteNonQuery(string cmdStr)
        {
            DbCommand cmd = this.CreateCommand(cmdStr);
            return ExecuteNonQuery(cmd);
        }
        /// <summary>
        /// 执行DbCommand.ExecuteNonQuery()。
        /// </summary>
        /// <param name="cmdStr">要执行的 SQL 语句</param>
        /// <param name="args">用于参与string.Format(cmdStr,args)的参数</param>
        /// <returns></returns>
        /// <remarks>
        /// 与<see cref="DbCommand.ExecuteScalar"/>的区别在于，它在执行前后会自动为你打开和关闭对应的数据库连接。
        /// </remarks>
        public int ExecuteNonQuery(string cmdStr, params object[] args)
        {
            string sql = string.Format(cmdStr, args);
            return ExecuteNonQuery(sql);
        }

        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="sqlStrs">多条SQL语句</param>
        /// <remarks>sqlStrs中的每项只能是一般SQL语句，不能为存储过程。</remarks>
        public void ExecuteWithTransaction(List<string> sqlStrs)
        {
            if (sqlStrs == null) throw new ArgumentNullException("sqlStrs");
            if (sqlStrs.Count < 1) throw new ArgumentException("There is no command to be executed.", "sqlStrs");

            List<DbCommand> cmds = new List<DbCommand>();
            foreach (string s in sqlStrs) {
                cmds.Add(this.CreateCommand(s));
            }
            ExecuteWithTransaction(cmds);
        }

        /// <summary>
        /// 执行多个SQL命令，实现数据库事务。
        /// </summary>
        /// <param name="cmds">多个SQL命令</param>
        /// <remarks>cmds中的所有命令必须使用相同的DbConnection</remarks>
        /// <exception cref="ArgumentNullException">"cmds" is null.</exception>
        /// <exception cref="ArgumentException">There is no command to be executed.</exception>
        /// <exception cref="ArgumentException">There must only one instant of DbConnection for the DbCommand(s).</exception>
        public void ExecuteWithTransaction(List<DbCommand> cmds)
        {
            if (cmds == null) throw new ArgumentNullException("cmds");
            if (cmds.Count < 1) throw new ArgumentException("There is no command to be executed.", "cmds");
            DbConnection conn = cmds[0].Connection;
            foreach (DbCommand c in cmds)
            {
                if (!c.Equals(conn))
                {
                    if (cmds.Count < 1) throw new ArgumentException("There must only one instant of DbConnection for the DbCommand(s).", "cmds");
                }
            }
            DbTransaction tr = null;
            try
            {
                conn.Open();
                tr = conn.BeginTransaction();
                foreach (DbCommand c in cmds)
                {
                    c.Connection = conn;
                    c.Transaction = tr;
                    c.ExecuteNonQuery();
                }
                tr.Commit();
            }
            catch
            {
                if (tr != null) tr.Rollback();
                throw;
            }
            finally
            {
                if (conn != null) conn.Close();
                conn.Dispose();
            }
        }

        #region IBaseProvider 成员
        /// <summary>
        /// 数据库连接所使用的驱动程序类型。
        /// </summary>
        public ConnectionType ConnectionType
        {
            get { return this.connType; }
            set { this.connType = value; }
        }

        /// <summary>
        /// 为数据库实例创建数据连接
        /// </summary>
        /// <remarks>调用此方法之后由此数据库实例所创建的DbCommand、DbDataAdapter、DbDataReader等对象将自动使用此连接。</remarks>
        public DbConnection CreateConnection(string connStr)
        {
            _connectionString = connStr;

            DbConnection _conn = connFactroy.CreateConnection();
            _conn.ConnectionString = connStr;
            return _conn;
        }

        /// <summary>
        /// 创建一个System.Data.Common.DbCommand对象
        /// </summary>
        /// <remarks>
        /// 所创建的DbCommand对象将自动使用数据库实例已有的连接。
        /// </remarks>
        /// <example>
        /// <code>
        /// Database db = new Database();
        /// DbCommand cmd = db.CreateCommand("SELECT * FROM [Table]");</code>
        /// </example>
        public DbCommand CreateCommand(string sqlStrTxt)
        {
            DbCommand cmd = CreateCommand();
            cmd.CommandText = sqlStrTxt;
            cmd.Connection = CreateConnection(_connectionString);
            return cmd;
        }
        /// <summary>
        /// 创建一个System.Data.Common.DbCommand对象
        /// </summary>
        /// <param name="sqlStrTxt">SQL语句</param>
        /// <param name="args">用于参与string.Format(sqlStrTxt,args)的参数</param>
        /// <remarks>
        /// 所创建的DbCommand对象将自动使用数据库实例已有的连接。
        /// </remarks>
        /// <example>
        /// <code>
        /// string tableName = "Table";
        /// Database db = new Database();
        /// DbCommand cmd = db.CreateCommand("SELECT * FROM [{0}]",tableName);</code>
        /// </example>
        public DbCommand CreateCommand(string sqlStrTxt, params object[] args)
        {
            return CreateCommand(string.Format(sqlStrTxt, args));
        }

        /// <summary>
        /// 创建一个System.Data.Common.DbCommand对象
        /// </summary>
        /// <param name="sqlStrTxt">SQL语句</param>
        /// <param name="parameters">参数数组</param>
        /// <remarks>
        /// 所创建的DbCommand对象将自动使用数据库实例已有的连接。
        /// </remarks>
        /// <example>
        /// <code>
        /// Database db = new Database();
        /// string sql = "SELECT * FROM [MYTABLE] WHERE Hit>@hit AND WriteDate>@date";
        /// DbParameter[] paras = {
        ///        db.CreateParameter("@hit", DbType.Int32,100)
        ///        ,db.CreateParameter("@date", DbType.DateTime,DateTime.Parse("2009-8-3"))
        /// };
        /// DbCommand cmd = db.CreateCommand(sql,paras);</code>
        /// </example>
        /// <returns></returns>
        public DbCommand CreateCommand(string sqlStrTxt, DbParameter[] parameters)
        {
            DbCommand cmd = CreateCommand(sqlStrTxt);
            cmd.Parameters.AddRange(parameters);
            return cmd;
        }

        public DbDataAdapter CreateDataAdapter(string sqlCmdTxt)
        {
            DbCommand cmd = CreateCommand(sqlCmdTxt);
            return CreateDataAdapter(cmd);
        }

        public DbDataReader CreateDataReader(string sqlCmdTxt)
        {
            DbCommand cmd = CreateCommand(sqlCmdTxt);
            return CreateDataReader(cmd);
        }

        public DbParameter CreateParameter(string name, DbType type, object value)
        {
            DbParameter para = CreateParameter();
            para.ParameterName = name;
            para.DbType = type;
            para.Value = value;
            return para;
        }
        /// <summary>
        /// 生成一个DbParameter参数实例
        /// </summary>
        /// <param name="name">参数名</param>
        /// <param name="type">参数类型。e.g.DbType.Int32,DbType.String</param>
        /// <param name="parameterSize">获取或设置列中数据的最大大小（以字节为单位）。祥见DbParameter.Size</param>
        /// <returns></returns>
        public DbParameter CreateOutPutParameter(string name, DbType type, int parameterSize)
        {
            DbParameter p = CreateParameter();
            p.ParameterName = name;
            p.DbType = type;
            p.Size = parameterSize;
            p.Direction = ParameterDirection.Output;
            return p;
        }

        /// <summary>
        /// 执行 SQL 语句返回数据集。
        /// </summary>
        /// <param name="sqlStrTxt">要执行的SQL语句</param>
        /// <returns></returns>
        /// <remarks>
        /// 一般用于只产生一个DataTable的单一SELECT语句，当且仅当返回的DataSet有数据行时才返回数据集，否则返回 null。
        /// 若使用了多条有返回DataTable的SELECT语句，请保证<see cref="DataSet"/>.Tables[0].Rows.Count > 0，否则将忽略其他表，也返回 null。
        /// </remarks>
        public DataSet SelectData(string sqlStrTxt)
        {
            DbCommand cmd = CreateCommand(sqlStrTxt);
            return SelectData(cmd);
        }

        /// <summary>
        /// 执行 SQL 语句返回数据集。
        /// </summary>
        /// <param name="sqlStrTxt">要执行的SQL语句</param>
        /// <param name="args">用于参与string.Format(sqlStrTxt,args)的参数</param>
        /// <returns></returns>
        /// <remarks>
        /// 一般用于只产生一个DataTable的单一SELECT语句，当且仅当返回的DataSet有数据行时才返回数据集，否则返回 null。
        /// 若使用了多条有返回DataTable的SELECT语句，请保证<see cref="DataSet"/>.Tables[0].Rows.Count > 0，否则将忽略其他表，也返回 null。
        /// </remarks>
        public DataSet SelectData(string sqlStrTxt, params object[] args)
        {
            DbCommand cmd = CreateCommand(string.Format(sqlStrTxt, args));
            return SelectData(cmd);
        }

        /// <summary>
        /// 执行 SQL 语句返回数据集。
        /// </summary>
        /// <param name="selectCommand"></param>
        /// <returns></returns>
        /// <remarks>
        /// 一般用于只产生一个DataTable的单一SELECT语句，当且仅当返回的DataSet有数据行时才返回数据集，否则返回 null。
        /// 若使用了多条有返回DataTable的SELECT语句，请保证<see cref="DataSet"/>.Tables[0].Rows.Count > 0，否则将忽略其他表，也返回 null。
        /// </remarks>
        public DataSet SelectData(DbCommand selectCommand)
        {
            if (selectCommand == null) throw new ArgumentNullException("selectCommand");

            using (DbDataAdapter adp = CreateDataAdapter(selectCommand))
            {
                DataSet ds = new DataSet();
                try
                {
                    if (selectCommand.Connection.State != ConnectionState.Closed) { selectCommand.Connection.Close(); }
                    //锁定以确保_conn.State的状态安全
                    object obj = new object();
                    lock (obj)
                    {
                        adp.Fill(ds);
                        if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
                        {
                            ds = null;
                        }
                    }
                    return ds;
                }
                catch
                {
                    throw;
                }
            }
        }


        /// <summary>
        /// 执行DbCommand.ExecuteNonQuery()。
        /// </summary>
        /// <param name="cmd">要执行的 SQL 命令</param>
        /// <returns></returns>
        /// <remarks>
        /// 与<see cref="DbCommand.ExecuteScalar"/>的区别在于，它在执行前后会自动为你打开和关闭对应的数据库连接。
        /// </remarks>
        public int ExecuteNonQuery(DbCommand cmd)
        {
            int tmp;
            try
            {
                if (cmd.Connection.State != ConnectionState.Open) { cmd.Connection.Close(); cmd.Connection.Open(); }
                tmp = cmd.ExecuteNonQuery();
                cmd.Connection.Close();
            }
            catch
            {
                throw;
            }
            return tmp;
        }

        /// <summary>
        /// 执行DbCommand.ExecuteScalar()。
        /// </summary>
        /// <param name="cmd">要执行的 SQL 命令</param>
        /// <returns></returns>
        /// <remarks>
        /// 与<see cref="DbCommand.ExecuteScalar"/>的区别在于，它在执行前后会自动为你打开和关闭对应的数据库连接。
        /// </remarks>
        public object ExecuteScalar(DbCommand cmd)
        {
            object tmp;
            try
            {
                if (cmd.Connection.State != ConnectionState.Open)
                {
                    cmd.Connection.Close(); cmd.Connection.Open();
                }
                tmp = cmd.ExecuteScalar();
                cmd.Connection.Close();
            }
            catch
            {
                throw;
            }
            return tmp;
        }

        #endregion
        #region IConnectionFactory 成员

        /// <summary>
        /// 创建一个System.Data.Common.DbCommand对象
        /// </summary>
        /// <remarks>所创建的DbCommand对象将自动使用数据库实例已有的连接。</remarks>
        public DbCommand CreateCommand()
        {
            DbCommand cmd = connFactroy.CreateCommand();
            cmd.Connection = this.CreateConnection(_connectionString);
            return cmd;
        }

        /// <summary>
        /// 为数据库实例创建数据连接
        /// </summary>
        /// <remarks>调用此方法之后由此数据库实例所创建的DbCommand、DbDataAdapter、DbDataReader等对象将自动使用此连接。</remarks>
        public DbConnection CreateConnection()
        {
            //if (_conn == null) _conn = connFactroy.CreateConnection();

            //永远都新构造一个链接，以免出现“阅读器关闭时尝试调用Read无效”的异常。
            //异常产生原因：多个地方的DbDataReader同时使用了同一个连接，而其中一个DbDataReader关闭连接将导致其他DbDataReader的连接也被关闭。

            DbConnection conn = connFactroy.CreateConnection();
            conn.ConnectionString = this._connectionString;
            return conn;
        }

        public DbDataAdapter CreateDataAdapter()
        {
            return connFactroy.CreateDataAdapter();
        }
        public DbDataAdapter CreateDataAdapter(DbCommand cmd)
        {
            return connFactroy.CreateDataAdapter(cmd);
        }

        public DbDataReader CreateDataReader(DbCommand cmd)
        {
            if (cmd.Connection.State != ConnectionState.Open) { cmd.Connection.Close(); cmd.Connection.Open(); }
            return connFactroy.CreateDataReader(cmd);
        }
        public DbDataReader CreateDataReader(DbCommand cmd, CommandBehavior behavior)
        {
            return connFactroy.CreateDataReader(cmd, behavior);
        }

        public DbCommandBuilder CreateCommandBuilder()
        {
            return connFactroy.CreateCommandBuilder();
        }

        public DbConnectionStringBuilder CreateConnectionStringBuilder()
        {
            return connFactroy.CreateConnectionStringBuilder();
        }

        public DbParameter CreateParameter()
        {
            return connFactroy.CreateParameter();
        }

        #endregion

    }
}
